package com.pine.dcts.metadata;

import com.google.common.collect.Lists;

import com.pine.dcts.common.DatasourceType;
import com.pine.dcts.common.DctsException;
import com.pine.dcts.entity.DatasourceInfo;
import com.pine.dcts.model.Field;
import com.pine.dcts.model.Index;
import com.pine.dcts.model.PrimaryKey;
import com.pine.dcts.model.Table;
import com.pine.dcts.utils.JdbcUtils;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 元数据信息分析默认实现类
 *
 * @author xiaoyuan
 * @create 2020/11/25 9:32
 **/
@Slf4j
public class MetadataAnalysis {
    /**
     * 元数据信息
     */
    private DatasourceInfo datasourceInfo;
    /**
     * 创建的连接
     */
    private Connection connection;
    /**
     * 获取的元数据信息
     */
    private DatabaseMetaData dbMetaData;

    public MetadataAnalysis(DatasourceInfo datasourceInfo) {
        this.datasourceInfo = datasourceInfo;
        if (datasourceInfo == null) {
            throw new DctsException("数据源信息为空");
        }
        connection = JdbcUtils.buildConnection(datasourceInfo);
        if (connection == null) {
            throw new DctsException("无法连接到该数据源[%s]", datasourceInfo.getJdbcUrl());
        }
        try {
            dbMetaData = connection.getMetaData();
        } catch (SQLException e) {
            log.error("获取数据源连接元数据信息错误", e);
        }
    }

    public DatasourceInfo getDatasourceInfo() {
        return datasourceInfo;
    }

    public Connection getConnection() {
        return connection;
    }

    public DatabaseMetaData getDbMetaData() {
        return dbMetaData;
    }

    public DatasourceType getDatasourceType() {
        return this.datasourceInfo.getType();
    }

    /**
     * 获取所有的数据库名称
     *
     * @return
     */
    public List<String> getCateLogs() {
        List<String> cateLogs = Lists.newArrayList();
        try {
            ResultSet rs = dbMetaData.getCatalogs();
            while (rs.next()) {
                cateLogs.add(rs.getString(1));
            }
        } catch (SQLException e) {
            log.error("获取数据库cateLog错误", e);
            throw new DctsException("获取数据库cateLog错误");
        }
        return cateLogs;
    }

    /**
     * 获得一个数据库下所有方案名称
     */
    public List<String> getAllSchemasByCateLog(String cateLog) {
        List<String> schemas = Lists.newArrayList();
        try {

            ResultSet rs = dbMetaData.getSchemas(cateLog, "%");
            while (rs.next()) {
                schemas.add(rs.getString("TABLE_SCHEM"));
            }
        } catch (SQLException e) {
            log.error("获取数据库Schema错误", e);
            throw new DctsException("获取数据库Schema错误");
        }
        return schemas;
    }

    /**
     * 获得该用户下面的所有表
     */
    public List<Table> getAllTableList(String catelog, String schemaName) {
        List<Table> tables = Lists.newArrayList();
        String[] types = {"TABLE", "VIEW"};
        try {
            ResultSet rs = dbMetaData.getTables(catelog, schemaName, "%", types);
            while (rs.next()) {
                // 表名
                String tableName = rs.getString("TABLE_NAME");
                // 表类型
                String tableType = rs.getString("TABLE_TYPE");
                // 表备注
                String remarks = rs.getString("REMARKS");
                Table table = new Table();
                table.setCatalogName(catelog);
                table.setSchemaName(schemaName);
                table.setRemark(remarks);
                table.setTableName(tableName.trim());
                table.setTableType(tableType);
                tables.add(table);
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]的表信息错误", catelog, e);
            throw new DctsException("获取数据库表信息错误");
        }
        return tables;
    }

    public List<Table> getAllFullTableList(String catelog, String schemaName) {
        List<Table> tables = Lists.newArrayList();
        String[] types = {"TABLE", "VIEW"};
        try {
            ResultSet rs = dbMetaData.getTables(catelog, schemaName, "%", types);
            while (rs.next()) {
                // 表名
                String tableName = rs.getString("TABLE_NAME");
                // 表类型
                String tableType = rs.getString("TABLE_TYPE");
                // 表备注
                String remarks = rs.getString("REMARKS");
                Table table = new Table();
                table.setCatalogName(catelog);
                table.setSchemaName(schemaName);
                table.setRemark(remarks);
                table.setTableName(tableName.trim());
                table.setTableType(tableType);
                table.setFields(getTableColumns(catelog, schemaName, tableName));
                table.setIndexes(getIndexInfo(catelog, schemaName, tableName));
                tables.add(table);
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]的表信息错误", catelog, e);
            throw new DctsException("获取数据库表信息错误");
        }
        return tables;
    }

    /**
     * 获取表的字段信息
     *
     * @param catelog
     * @param schemaName
     * @param tableName
     * @return
     */
    public List<Field> getTableColumns(String catelog, String schemaName, String tableName) {
        List<Field> fields = Lists.newArrayList();
        List<PrimaryKey> primaryKeys = getAllPrimaryKeys(catelog, schemaName, tableName);
        try {

            ResultSet rs = dbMetaData.getColumns(catelog, schemaName, tableName, "%");
            while (rs.next()) {
                // 表目录（可能为空）
                String tableCat = rs.getString("TABLE_CAT");
                // 表的架构（可能为空）
                String tableSchemaName = rs.getString("TABLE_SCHEM");
                // 表名
                String tableName_ = rs.getString("TABLE_NAME");
                // 列名
                String columnName = rs.getString("COLUMN_NAME");
                // 对应的java.sql.Types类型
                int dataType = rs.getInt("DATA_TYPE");
                // java.sql.Types类型名称
                String dataTypeName = rs.getString("TYPE_NAME");
                // 列大小
                int columnSize = rs.getInt("COLUMN_SIZE");
                // 小数位数
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                // 基数（通常是10或2）
                int numPrecRadix = rs.getInt("NUM_PREC_RADIX");
                // 是否允许为null
                int nullAble = rs.getInt("NULLABLE");
                // 列描述
                String remarks = rs.getString("REMARKS");
                // 默认值
                String columnDef = rs.getString("COLUMN_DEF");
                // sql数据类型
                int sqlDataType = rs.getInt("SQL_DATA_TYPE");
                // SQL日期时间分?
                int sqlDatetimeSub = rs.getInt("SQL_DATETIME_SUB");
                // char类型的列中的最大字节数
                int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
                // 表中列的索引（从1开始）
                int ordinalPosition = rs.getInt("ORDINAL_POSITION");

                /**
                 * ISO规则用来确定某一列的为空性。 是---如果该参数可以包括空值; 无---如果参数不能包含空值
                 * 空字符串---如果参数为空性是未知的
                 */
                boolean isNullAble = "NO".equalsIgnoreCase(rs.getString("IS_NULLABLE")) ? false : true;

                /**
                 * 指示此列是否是自动递增 是---如果该列是自动递增 无---如果不是自动递增列 空字串---如果不能确定它是否
                 * 列是自动递增的参数是未知
                 */
                boolean isAutoincrement = "NO".equalsIgnoreCase(rs.getString("IS_AUTOINCREMENT")) ? false : true;


                Field field = new Field();
                field.setAutoincrement(isAutoincrement);
                field.setCatalogName(catelog);
                field.setColumnName(columnName);
                field.setDecimalDigits(decimalDigits);
                field.setDefaultValue(columnDef);
                field.setJdbcType(dataType);
                field.setFieldComment(remarks);
                field.setNullable(isNullAble);
                field.setOrdinalPosition(ordinalPosition);
                field.setSchemaName(schemaName);
                field.setTableName(tableName);
                field.setSize(columnSize);
                field.setSqlTypeName(dataTypeName);
                field.setPrimaryKey(primaryKeys.stream().anyMatch(r -> r.getColumnName().trim().equalsIgnoreCase(columnName)));
                fields.add(field);
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]的表[{}]的字段错误", catelog, tableName, e);
            throw new DctsException("获取数据库索引错误");
        }
        return fields;
    }

    /**
     * 获取表的索引信息
     *
     * @param catelog
     * @param schemaName
     * @param tableName
     * @return
     */
    public List<Index> getIndexInfo(String catelog, String schemaName, String tableName) {
        List<Index> indices = Lists.newArrayList();
        try {
            ResultSet rs = dbMetaData.getIndexInfo(catelog, schemaName, tableName, true, true);
            while (rs.next()) {
                // 非唯一索引(Can
                // index values
                // be
                // non-unique.
                // false when
                // TYPE is
                // tableIndexStatistic
                // )
                boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                // 索引目录（可能为空）
                String indexQualifier = rs.getString("INDEX_QUALIFIER");
                // 索引的名称
                String indexName = rs.getString("INDEX_NAME");
                // 索引类型
                short type = rs.getShort("TYPE");
                // 在索引列顺序号
                short ordinalPosition = rs.getShort("ORDINAL_POSITION");
                // 列名
                String columnName = rs.getString("COLUMN_NAME");
                // 列排序顺序:升序还是降序
                String ascOrDesc = rs.getString("ASC_OR_DESC");
                // 基数
                int cardinality = rs.getInt("CARDINALITY");
                Index index = new Index();
                index.setCatalogName(catelog);
                index.setSchemaName(schemaName);
                index.setTableName(tableName);
                index.setColumnName(columnName);
                index.setIndexType(type);
                index.setIndexName(indexName);
                index.setNonUnique(nonUnique);
                index.setOrdinalPosition(ordinalPosition);
                indices.add(index);
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]的表[{}]的索引错误", catelog, tableName, e);
            throw new DctsException("获取数据库索引错误");
        }
        return indices;
    }

    /**
     * 获取表的主键信息
     *
     * @param catelog
     * @param schemaName
     * @param tableName
     * @return
     */
    public List<PrimaryKey> getAllPrimaryKeys(String catelog, String schemaName, String tableName) {
        List<PrimaryKey> primaryKeys = Lists.newArrayList();
        try {
            ResultSet rs = dbMetaData.getPrimaryKeys(catelog, schemaName, tableName);
            while (rs.next()) {
                primaryKeys.add(new PrimaryKey(rs.getString("COLUMN_NAME"), rs.getShort("KEY_SEQ"), rs.getString("PK_NAME")));
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]的表[{}]的索引错误", catelog, tableName, e);
            throw new DctsException("获取数据库Schema错误");
        }
        return primaryKeys;
    }
}
